# -*- coding: utf-8 -*-
# @Time    : 2019/10/25 13:53
# @Author  : CHEN Wang
# @Site    : 
# @File    : index_price_related.py
# @Software: PyCharm

"""
脚本说明: 获取指数行情数据
"""

import pandas as pd
from quant_researcher.quant.project_tool.db_operator import my_mysql, db_conn
from quant_researcher.quant.project_tool.wrapper_tools import common_wrappers, conn_wrappers
from quant_researcher.quant.project_tool import time_tool
from quant_researcher.quant.project_tool.celebrity import get_lst_condition
from quant_researcher.quant.project_tool.logger.my_logger import LOG

T_STOCK_INDEX_QUOTE_1 = 'idx_mkt_quote'
T_STOCK_INDEX_QUOTE_2 = 'idx_hsmkt_daily_quote'
T_BOND_INDEX_QUOTE = 'idx_bond_indicator_data'
T_INDEX_DAILY_RETURN = 'mf_di_idxdailyreturn'
T_INDEX_MONTHLY_RETURN = 'mf_di_idxmonthlyreturn'
EARLIEST_START_DATE = '1900-01-01'


def get_index_close(index_code, start_date=None, end_date=None, only_latest=False, freq='D', **kwargs):
    """
    得到指数的收盘价，包括股票指数、债券指数、自建指数

    :param str or list index_code: 指数代码，支持单个（str）、多个（list）
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一天数据，False-获取一个区间段的数据
    :param str freq: 收盘价的频率，D-日频；M-月频
    :param kwargs
            conn: 数据库连接
    :return: pd.DataFrame
                - index_code: 指数代码
                - end_date: 交易日期
                - close: 收盘价
    """
    conn = kwargs.pop('conn', None)

    res = []

    if isinstance(index_code, str):
        index_code = [index_code]

    for a_code in index_code:
        if a_code[:3] == 'CBA':
            table = T_BOND_INDEX_QUOTE
            date_col = 'trade_date'
            close_col = 'cfzs_val'
        elif a_code[:2] == 'TK':
            table = T_INDEX_DAILY_RETURN
            date_col = '`day`'
            close_col = 'price_to'
        else:
            table = T_STOCK_INDEX_QUOTE_1
            date_col = 'trade_date'
            close_col = 'close'

        select = ['index_code', f'{date_col} as end_date', f'{close_col} as close']

        where = []

        # index_code过滤条件
        index_where_condition = f"index_code = '{a_code}'"
        where.append(index_where_condition)

        # 将日期转换成带杠字符串
        if start_date is None:
            start_date = EARLIEST_START_DATE.replace('-', '')
        else:
            start_date = time_tool.format_date_str(start_date, '%Y%m%d')
        if end_date is None:
            end_date = time_tool.get_today(marker='without_n_dash')
        else:
            end_date = time_tool.format_date_str(end_date, '%Y%m%d')

        if table == T_INDEX_DAILY_RETURN:
            tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
        else:
            tmp_conn = db_conn.get_basic_data_conn() if conn is None else conn
        # 只返回最新数据
        if only_latest:
            select = ', '.join(select)
            sql_str = (f"select {select} from {table} as a, "
                       f"(select index_code as code, max({date_col}) as enddate "
                       f"from {table} "
                       f"where {date_col} <= '{end_date}' "
                       f"and {index_where_condition} "
                       f"group by index_code) as b "
                       f"where a.index_code = b.code "
                       f"and a.{date_col} = b.enddate ")
            df = my_mysql.read_v3(sql_str, tmp_conn)
        # 返回一段时间数据的where语句
        else:
            where.append(f"{date_col} >= '{start_date}'")
            where.append(f"{date_col} <= '{end_date}'")
            df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

        if conn is None:
            tmp_conn.close()

        if df.empty:
            LOG.error(f"没有找到指数{a_code}的收盘价数据，请检查")
            continue

        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

        res.append(df)

    if len(res) == 0:
        LOG.error(f"没有找到{index_code}任一指数的收盘价数据，请检查")
        return

    res = pd.concat(res)

    if freq == 'M':
        res = res.set_index('end_date')
        res.index = pd.to_datetime(res.index)
        res = res.groupby('index_code').apply(lambda x: x.asfreq('M', 'ffill'))
        res = res.reset_index(level=0, drop=True).reset_index()
        res['end_date'] = res['end_date'].apply(lambda x: time_tool.format_date_str(x))
    return res


def get_stock_index_quote(index_code=None, start_date=None, end_date=None, only_latest=False, select=None):
    """
    获得股票型指数的行情相关数据

    :param str or list or None index_code: 指数代码，支持单个（str）、多个（list）、所有（None）
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一天数据，False-获取一个区间段的数据
    :param list select: 需要返回哪些列，若为None，默认返回所有列
    :return: pd.DataFrame
                - index_code: 指数代码
                - end_date: 交易日期
                - preclose: 前收盘价
                - open: 开盘价
                - high: 最高价
                - low: 最低价
                - close: 收盘价
                - trade_volumen: 成交量(股)
                - trade_amount: 成交金额(元)
                - wave_range: 涨跌
                - day_price_limit: 涨跌幅(%)
    """
    if select is None:
        select = ['index_code', 'trade_date as end_date', 'preclose', 'open', 'high',
                  'low', 'close', 'trade_volumen', 'trade_amount', 'wave_range', 'day_price_limit']

    where = []

    # index_code过滤条件
    if isinstance(index_code, str) or isinstance(index_code, list):
        if isinstance(index_code, str):
            index_code = [index_code]
        index_where_condition = get_lst_condition(index_code, 'index_code')
        where.append(index_where_condition)
    elif index_code is None:
        index_where_condition = '1 = 1'
    else:
        LOG.error('目前index_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE.replace('-', '')
    else:
        start_date = time_tool.format_date_str(start_date, '%Y%m%d')
    if end_date is None:
        end_date = time_tool.get_today(marker='without_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    conn = db_conn.get_basic_data_conn()
    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {T_STOCK_INDEX_QUOTE_1} as a, "
                   f"(select index_code as code, max(trade_date) as enddate "
                   f"from {T_STOCK_INDEX_QUOTE_1} "
                   f"where trade_date <= '{end_date}' "
                   f"and {index_where_condition} "
                   f"group by index_code) as b "
                   f"where a.index_code = b.code "
                   f"and a.trade_date = b.enddate ")
        df = my_mysql.read_v3(sql_str, conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"trade_date >= '{start_date}'")
        where.append(f"trade_date <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=T_STOCK_INDEX_QUOTE_1, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{index_code}指数的数据，请检查")
        return

    if 'end_date' in df.columns:
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


def get_bond_index_quote(index_code=None, start_date=None, end_date=None, only_latest=False, select=None, **kwargs):
    """
    获得债券型指数的行情相关数据

    :param str or list or None index_code: 指数代码，支持单个（str）、多个（list）、所有（None）
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一天数据，False-获取一个区间段的数据
    :param list select: 需要返回哪些列，若为None，默认返回所有列
    :param kwargs:
        - conn: 数据库连接
    :return: pd.DataFrame
                - index_code: 指数代码
                - trade_date: 交易日期
                - cfzs_val: 财富指数收盘价
                - pjszfjq_val: 平均市值法久期
                - pjxjlfjq_val: 平均现金流法久期
                - pjszftx_val: 平均市值法凸性
                - pjxjlftx_val: 平均现金流法凸性
                - pjdqsyl_val: 平均到期收益率
                - pjszfdqsyl_val: 平均市值法到期收益率
    """
    conn = kwargs.pop('conn', None)

    if select is None:
        select = ['index_code', 'trade_date as end_date', 'cfzs_val', 'pjszfjq_val', 'pjxjlfjq_val',
                  'pjszftx_val', 'pjxjlftx_val', 'pjdqsyl_val', 'pjszfdqsyl_val']

    where = []

    # index_code过滤条件
    if isinstance(index_code, str) or isinstance(index_code, list):
        if isinstance(index_code, str):
            index_code = [index_code]
        index_where_condition = get_lst_condition(index_code, 'index_code')
        where.append(index_where_condition)
    elif index_code is None:
        index_where_condition = '1 = 1'
    else:
        LOG.error('目前index_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE.replace('-', '')
    else:
        start_date = time_tool.format_date_str(start_date, '%Y%m%d')
    if end_date is None:
        end_date = time_tool.get_today(marker='without_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    tmp_conn = db_conn.get_basic_data_conn() if conn is None else conn
    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {T_BOND_INDEX_QUOTE} as a, "
                   f"(select index_code as code, max(trade_date) as enddate "
                   f"from {T_BOND_INDEX_QUOTE} "
                   f"where trade_date <= '{end_date}' "
                   f"and {index_where_condition} "
                   f"group by index_code) as b "
                   f"where a.index_code = b.code "
                   f"and a.trade_date = b.enddate ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"trade_date >= '{start_date}'")
        where.append(f"trade_date <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=T_BOND_INDEX_QUOTE, conn=tmp_conn)
    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到{index_code}指数的数据，请检查")
        return

    if 'end_date' in df.columns:
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


def calc_index_daily_return(index_code, end_date):
    """
    得到某一天指定指数列表中每个指数的涨跌幅
    idx_hsmkt_daily_quote这个表中的指数没有计算收益率到mysql的表，如果需要用这个表中指数的日收益率需要调用此函数

    :param str or list index_code: 指数代码，支持单个（str）、多个（list）
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :return: pd.DataFrame
                - index_code: 指数代码
                - ret: 日收益率
    """
    end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    conn = db_conn.get_basic_data_conn()

    if isinstance(index_code, str):
        index_code = [index_code]
    index_where_condition = get_lst_condition(index_code, 'index_code')

    date_sql_str = (f"select distinct trade_date "
                    f"from {T_STOCK_INDEX_QUOTE_2} "
                    f"where trade_date <= '{end_date}' "
                    f"and {index_where_condition} "
                    f"order by trade_date desc "
                    f"limit 2 ")
    date_4_index = my_mysql.read_v3(date_sql_str, conn)['trade_date'].tolist()

    if len(date_4_index) != 2:
        index_ret = pd.DataFrame({'index_code': index_code})
        index_ret['ret'] = 0
        conn.close()
        return index_ret

    index_hq = my_mysql.read_v2(select=['index_code', 'trade_date as tj', 'close'],
                                sfrom=T_STOCK_INDEX_QUOTE_2,
                                where=[f"trade_date in {tuple(date_4_index)}", index_where_condition],
                                conn=conn)
    index_hq = index_hq.set_index(['index_code', 'tj'])['close'].unstack()
    index_hq['ret'] = index_hq[date_4_index[0]] / index_hq[date_4_index[1]] - 1
    index_ret = index_hq[['ret']]
    # 防止有的指数断更
    index_ret = index_ret.reindex(index_code)
    index_ret = index_ret.fillna(0)
    index_ret = index_ret.reset_index()
    conn.close()
    return index_ret


def calc_index_return(index_code, start_date, end_date):
    # todo 该函数中可以直接调用get_index_close函数获取收盘信息

    end_date = end_date.replace('-', '')
    start_date = start_date.replace('-', '')

    conn_base = db_conn.get_basic_data_conn()
    index_data = pd.read_sql(f"select index_code, trade_date, close "
                             f"from idx_mkt_quote "
                             f"where index_code in {tuple(index_code)} "
                             f"and trade_date between '{start_date}' and '{end_date}' ", conn_base)
    index_data = index_data.drop_duplicates(subset=['index_code', 'trade_date'])

    bond_index_data = pd.read_sql(f"select index_code, trade_date, cfzs_val as close "
                                  f"from idx_bond_indicator_data "
                                  f"where index_code in {tuple(index_code)} "
                                  f"and trade_date between '{start_date}' and '{end_date}' ", conn_base)
    bond_index_data = bond_index_data.drop_duplicates(subset=['index_code', 'trade_date'])

    # 合并指数行情
    index_data = pd.concat([index_data, bond_index_data], sort=False, ignore_index=True)

    index_data = index_data.drop_duplicates(subset=['index_code', 'trade_date'])
    index_data.set_index(['trade_date', 'index_code'], inplace=True)
    index_data = index_data['close'].unstack()
    index_data = index_data[index_code]
    index_data.index = pd.to_datetime(index_data.index)

    return_table = index_data.pct_change()
    return_table = return_table.reindex(index=return_table.index[::-1])

    conn_base.close()

    return return_table


def get_index_return(index_code=None, start_date=None, end_date=None, only_latest=False, freq='D', **kwargs):
    """
    获得指数的日收益率或者月收益率

    :param str or list or None index_code: 指数代码，支持单个（str）、多个（list）、所有（None）
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param bool only_latest: True-只获取最新的一期数据，False-获取一个区间段的数据
    :param str freq: D-日收益率，返回列名daily_return；M-月收益率，返回列名monthly_return
    :param kwargs
            conn: 数据库连接
    :return: pd.DataFrame
                +-------------+------------+----------------+
                |  index_code |  end_date  | monthly_return |
                +-------------+------------+----------------+
                | CBA00301.CS | 2020-07-31 |    -0.0075     |
                | CBA00301.CS | 2020-08-31 |    -0.0027     |
                | CBA00301.CS | 2020-09-30 |    -0.0017     |
                | CBA00301.CS | 2020-10-31 |     0.0036     |
                | CBA00301.CS | 2020-11-30 |     0.0025     |
                +-------------+------------+----------------+
    """
    conn = kwargs.pop('conn', None)
    tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn

    if freq == 'D':
        table = T_INDEX_DAILY_RETURN
        date_col = '`day`'
        ret_col = 'daily_return'
    elif freq == 'M':
        table = T_INDEX_MONTHLY_RETURN
        date_col = '`month`'
        ret_col = 'monthly_return'
    else:
        raise NotImplementedError('目前freq只支持D和M')

    select = ['index_code', f'{date_col} as end_date', ret_col]

    where = []

    # fund_code过滤条件
    if isinstance(index_code, str) or isinstance(index_code, list):
        if isinstance(index_code, str):
            index_code = [index_code]
        index_where_condition = get_lst_condition(index_code, 'index_code')
        where.append(index_where_condition)
    elif index_code is None:
        index_where_condition = '1 = 1'
    else:
        LOG.error('目前index_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)
    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select index_code as code, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {index_where_condition} "
                   f"group by index_code) as b "
                   f"where a.index_code = b.code "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到指数{index_code}的收益率数据，请检查")
        return

    if freq == 'D':
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)

    return df


def self_index_return(self_index_info, return_df):
    """
    构建指数

    :param dict self_index_info: 自建指数成分及权重，如{'CBA00301.CS': 0.5, '000300': 0.5}
    :param pd.Dataframe return_df: 指数收益率,日频，或者月频
                    +-------------+------------+----------------+
                |  index_code |  date        |     ret   |
                +-------------+------------+----------------+
                | CBA00301.CS | 2020-07-31 |    -0.0075     |
                | CBA00301.CS | 2020-08-31 |    -0.0027     |
                | CBA00301.CS | 2020-09-30 |    -0.0017     |
                | CBA00301.CS | 2020-10-31 |     0.0036     |
                | CBA00301.CS | 2020-11-30 |     0.0025     |
                +-------------+------------+----------------+
    :return:pd.DataFrame
                +------------+----------------+
                             |        0       |
                +------------+----------------+
                | 2020-07-31 |    -0.0075     |
                | 2020-08-31 |    -0.0027     |
                | 2020-09-30 |    -0.0017     |
                | 2020-10-31 |     0.0036     |
                | 2020-11-30 |     0.0025     |
               +------------+----------------+

    """

    index_list = list(self_index_info.keys())  # 复合指数包含的成分指数
    weights = pd.DataFrame([self_index_info]).T

    # 成分指数收益率数据提取
    index_return_df = return_df[return_df['index_code'].isin(index_list)]

    # 获取成分指数的最早行情日期,取所有成分股成立日期中最晚的日期作为混合指数的成立日期
    index_earliest_date = []
    for index in index_list:
        date = index_return_df[index_return_df['index_code'] == index]['date'].min()
        index_earliest_date.append(date)
    com_index_start_date = max(index_earliest_date)

    # 获取混合指数成立日期以后的成分指数行情数据
    index_return_df = index_return_df[index_return_df['date'] >= com_index_start_date]

    index_return_df = index_return_df.set_index(['date', 'index_code'])['ret'].unstack()
    index_return_df.fillna(0, inplace=True)  # 某些日期有些指数有行情，有些指数没有行情，没有行情的收益填充为0

    # 计算混合指数收益率数据
    self_index_df = index_return_df.dot(weights)

    return self_index_df


"""
之前的API函数，目前都替换成最新的API函数，以下函数已弃用
"""

# C_C = 'index_code'
# C_DATE_ORIGIN = 'trade_date'
# C_PRICE = 'close'
# C_P_DAILY = 'daily_return'
# C_DATE_DAILY = '`day`'
# C_P_MONTHLY = 'monthly_return'
# C_DATE_MONTHLY = '`month`'


# def get_index_close(index_code, start_date, end_date):
#     """
#     获取指数一段区间内的收盘价，包括股票指数、债券指数、自建指数
#
#     :param index_code: 指数代码，支持单个(str)多个(list)全部(None)
#     :param str start_date: 开始时间，格式"2020-08-01"，若只查询一天，则start_date=None，该天用end_date传入
#     :param str end_date: 结束时间，格式"2020-08-05"
#     :return:
#     """
#     select = ['index_code', '`day` as trade_date', 'price_to as close']
#
#     where = []
#     if isinstance(index_code, str):
#         where.append(f'index_code=\'{index_code}\'')
#     elif isinstance(index_code, list):
#         where.append(get_lst_condition(index_code, 'index_code'))
#     elif index_code is None:
#         pass
#     else:
#         LOG.error('目前index_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     if start_date is None:
#         where.append(f"day = '{end_date}'")
#     else:
#         where.append(f"day >= '{start_date}'")
#         where.append(f"day <= '{end_date}'")
#
#     conn = db_conn.get_derivative_data_conn()
#
#     df = my_mysql.read_v2(select=select, where=where, sfrom=T_INDEX_DAILY_RETURN, conn=conn)
#     df = df.set_index(['trade_date', 'index_code'])['close'].unstack()
#     df.index = pd.to_datetime(df.index)
#     if isinstance(index_code, list):
#         df = df[index_code]
#     conn.close()
#
#     return df
#
#
# def get_stock_index_quote(index_code, start_date, end_date,
#                           select=None, order_by=None):
#     """
#     获得股票型指数的行情相关数据
#
#     :param index_code: 指数代码，支持单个(str)多个(list)全部(None)
#     :param str start_date: 开始时间，格式"2020-08-01"，若只查询一天，则start_date=None，该天用end_date传入
#     :param str end_date: 结束时间，格式"2020-08-05"
#     :param list select: 需要返回哪些列，若为None，默认返回指数代码、交易日期、收盘价
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :return: pd.DataFrame
#                 - index_code: 指数代码
#                 - trade_date: 交易日期 str "20200801"
#                 - preclose: 前收盘价
#                 - open: 开盘价
#                 - high: 最高价
#                 - low: 最低价
#                 - close: 收盘价
#                 - trade_volumen: 成交量(股)
#                 - trade_amount: 成交金额(元)
#                 - wave_range: 涨跌
#                 - day_price_limit: 涨跌幅(%)
#     """
#     if select is None:
#         select = ['index_code', 'trade_date', 'close']
#
#     where = []
#     if isinstance(index_code, str):
#         where.append(f'index_code=\'{index_code}\'')
#     elif isinstance(index_code, list):
#         where.append(get_lst_condition(index_code, 'index_code'))
#     elif index_code is None:
#         pass
#     else:
#         LOG.error('目前index_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     end_date = end_date.replace('-', '')
#     if start_date is None:
#         where.append(f"trade_date = '{end_date}'")
#     else:
#         start_date = start_date.replace('-', '')
#         where.append(f"trade_date >= '{start_date}'")
#         where.append(f"trade_date <= '{end_date}'")
#
#     conn = db_conn.get_basic_data_conn()
#
#     df = my_mysql.read_v2(select=select, where=where, sfrom=T_STOCK_INDEX_QUOTE_1, order_by=order_by, conn=conn)
#
#     conn.close()
#
#     return df
#
#
# def get_bond_index_quote(index_code, start_date, end_date,
#                          select=None, order_by=None):
#     """
#     获得债券型指数的行情相关数据
#
#     :param index_code: 指数代码，支持单个(str)多个(list)全部(None)
#     :param str start_date: 开始时间，格式"2020-08-01"，若只查询一天，则start_date=None，该天用end_date传入
#     :param str end_date: 结束时间，格式"2020-08-05"
#     :param list select: 需要返回哪些列，若为None，默认返回指数代码、交易日期、收盘价
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :return: pd.DataFrame
#                 - index_code: 指数代码
#                 - trade_date: 交易日期 str "20200801"
#                 - cfzs_val: 财富指数收盘价
#                 - pjszfjq_val: 平均市值法久期
#                 - pjxjlfjq_val: 平均现金流法久期
#                 - pjszftx_val: 平均市值法凸性
#                 - pjxjlftx_val: 平均现金流法凸性
#                 - pjdqsyl_val: 平均到期收益率
#                 - pjszfdqsyl_val: 平均市值法到期收益率
#     """
#     if select is None:
#         select = ['index_code', 'trade_date', 'close']
#
#     where = []
#     if isinstance(index_code, str):
#         where.append(f'index_code=\'{index_code}\'')
#     elif isinstance(index_code, list):
#         where.append(get_lst_condition(index_code, 'index_code'))
#     elif index_code is None:
#         pass
#     else:
#         LOG.error('目前index_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     end_date = end_date.replace('-', '')
#     if start_date is None:
#         where.append(f"trade_date = '{end_date}'")
#     else:
#         start_date = start_date.replace('-', '')
#         where.append(f"trade_date >= '{start_date}'")
#         where.append(f"trade_date <= '{end_date}'")
#
#     conn = db_conn.get_basic_data_conn()
#
#     df = my_mysql.read_v2(select=select, where=where, sfrom=T_BOND_INDEX_QUOTE, order_by=order_by, conn=conn)
#
#     conn.close()
#
#     return df
#
#
# def get_index_daily_return(index_code, start=None, end=None, order_by=None, **kwargs):
#     """
#     得到指定指数的日收益率
#
#     :param index_code: 如果是指数代码str，返回该代码指数的日收益率
#                        如果是指数代码list，返回该指数列表中所有指数的日收益率
#                        对于指数来说，应该没有要查询表中所有指数收益率的需求，所以这里比基金的收益率函数少一个参数
#     :param str start: 查询开始时间，格式'2020-08-01'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09-01'
#     :param str order_by: 按什么字段升序排列
#     :param kwargs:
#         - return_only_price_col: 只返回收益率字段，默认True，请不要修改默认值，以免影响其他脚本
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#         - replace_price: 是否将i_price字段名称修改为daily_return，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     return_only_price_col = kwargs.pop('return_only_price_col', True)
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#     replace_price = kwargs.pop('replace_price', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     c_price = f'{C_P_DAILY} as i_price'
#     where = []
#
#     if return_only_price_col:
#         select = c_price
#     else:
#         select = [f'{C_DATE_DAILY} as tj', C_C, c_price]
#
#     if isinstance(index_code, str):
#         where.append(f'{C_C}=\'{index_code}\'')
#     elif isinstance(index_code, list):
#         where.append(get_lst_condition(index_code, 'index_code'))
#     else:
#         LOG.error('目前index_code只支持str或者list，请按要求输入')
#         raise NotImplementedError
#
#     if start is not None:
#         where.append(f'{C_DATE_DAILY}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_DATE_DAILY}<=\'{end}\'')
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_INDEX_DAILY_RETURN,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#     if replace_price:
#         df = df.rename(columns={'i_price': 'daily_return'})
#
#     return df
#
#
# def get_index_monthly_return(index_code, start=None, end=None, order_by=None, **kwargs):
#     """
#     得到指定指数的月收益率
#
#     :param index_code: 如果是指数代码str，返回该代码指数的日收益率
#                        如果是指数代码list，返回该指数列表中所有指数的日收益率
#     :param str start: 查询开始时间，格式'2020-08'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09'
#     :param str order_by: 按什么字段升序排列
#     :param kwargs:
#         - return_only_price_col: 只返回收益率字段，默认True，请不要修改默认值，以免影响其他脚本
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#         - replace_price: 是否将i_price字段名称修改为monthly_return，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     return_only_price_col = kwargs.pop('return_only_price_col', True)
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#     replace_price = kwargs.pop('replace_price', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     c_price = f'{C_P_MONTHLY} as i_price'
#     if return_only_price_col:
#         select = c_price
#     else:
#         select = [f'{C_DATE_MONTHLY} as tj', C_C, c_price]
#     where = []
#
#     if isinstance(index_code, str):
#         where.append(f'{C_C}=\'{index_code}\'')
#     elif isinstance(index_code, list):
#         where.append(get_lst_condition(index_code, 'index_code'))
#     else:
#         LOG.error('目前index_code只支持str或者list，请按要求输入')
#         raise NotImplementedError
#
#     if start is not None:
#         where.append(f'{C_DATE_MONTHLY}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_DATE_MONTHLY}<=\'{end}\'')
#
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_INDEX_MONTHLY_RETURN,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#     if replace_price:
#         df = df.rename(columns={'i_price': 'monthly_return'})
#
#     return df


if __name__ == '__main__':
    # aaa = get_index_close(['H11025', 'TK10TYPE1', 'CBA00301.CS'], '2020-01-01', '2020-05-01', only_latest=False, freq='M')
    # bbb = get_stock_index_quote('000300', '2020-01-01', '2020-05-01', True)
    # ccc = get_bond_index_quote('CBA00301.CS', '2020-01-01', '2020-05-01', False)
    ddd = calc_index_daily_return(['000012', '000013'], '2021-01-11')
    # eee = get_index_return(['000001', '000300', 'CBA00301.CS'], start_date='2019-12-01',
    #                        end_date='2020-11-30', only_latest=False, freq='M')
    # from quant_researcher.quant.project_tool import hammer
    #
    # hammer.slim(eee.tail(), a_f=1)